﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleMap :ICmsArticleMap
    {
        private const string ADD_SQL = "insert into COFCO_ARTICLE_MAP (ARTICLE_ID,MAP_NAME,MAP_LAT,MAP_LOG,MAP_CONTENT,ADDUSER,ADDTIME,STATE) values (@ARTICLE_ID,@MAP_NAME,@MAP_LAT,@MAP_LOG,@MAP_CONTENT,@ADDUSER,getdate(),@STATE);select @@IDENTITY";
        /// <summary>
        /// 添加
        /// </summary>
        /// <returns></returns>
        public int Add(CmsArticleMapModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@ARTICLE_ID", SqlDbType.Int),
                new SqlParameter("@MAP_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@MAP_LAT", SqlDbType.VarChar,100),
                new SqlParameter("@MAP_LOG", SqlDbType.VarChar,50),
                new SqlParameter("@MAP_CONTENT", SqlDbType.Text),
                new SqlParameter("@ADDUSER", SqlDbType.VarChar,50),
                new SqlParameter("@STATE", SqlDbType.Int),
                new SqlParameter("@ORDER_NUM", SqlDbType.Int)
            };
            parms[0].Value = model.ArticleId;
            parms[1].Value = model.MapName;
            parms[2].Value = model.MapLat;
            parms[3].Value = model.MapLog;
            parms[4].Value = model.MapContent;
            parms[5].Value = model.AddUser;
            parms[6].Value = model.State;
            parms[7].Value = model.Ordernum;

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();
                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return id;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public bool Delete(int mid)
        {
            int i = SQLHelper.ExecuteSql("delete from COFCO_ARTICLE_MAP where MAP_ID=" + mid, new SqlParameter[] { });
            if (i > 0) { return true; }
            return false;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <returns></returns>
        public bool Update(CmsArticleMapModel model)
        {
            string UPDATE_SQL = "update COFCO_ARTICLE_MAP set MAP_NAME=@MAP_NAME,MAP_LAT=@MAP_LAT,MAP_LOG=@MAP_LOG,MAP_CONTENT=@MAP_CONTENT,STATE=@STATE,ORDER_NUM=@ORDER_NUM where MAP_ID=@MAP_ID";
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@MAP_NAME",SqlDbType.VarChar,50),
                new SqlParameter("@MAP_LAT",SqlDbType.VarChar,100),
                new SqlParameter("@MAP_LOG",SqlDbType.VarChar,50),
                new SqlParameter("@MAP_CONTENT",SqlDbType.Text),
                new SqlParameter("@STATE",SqlDbType.Int),
                new SqlParameter("@ORDER_NUM", SqlDbType.Int),
                new SqlParameter("@MAP_ID",SqlDbType.Int)
                
            };

            parms[0].Value = model.MapName;
            parms[1].Value = model.MapLat;
            parms[2].Value = model.MapLog;
            parms[3].Value = model.MapContent;
            parms[4].Value = model.State;
            parms[5].Value = model.Ordernum;
            parms[6].Value = model.MapId;

            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <returns></returns>
        public IList GetMapList(string str)
        {
            string qsql = "select MAP_ID,ARTICLE_ID,MAP_NAME,MAP_LAT,MAP_LOG,MAP_CONTENT,ADDUSER,ADDTIME,[STATE],ORDER_NUM from COFCO_ARTICLE_MAP where " + str;
            //if (model != null)
            //{
            //    if (model.MapName != null && model.MapName.Trim() != "")
            //    {
            //        qsql += " and MAP_NAME like '%" + model.MapName + "%'";
            //    }
            //    if (model.State > 0)
            //    {
            //        qsql += " and STATE=1";
            //    }
            //}
            if (!(str.ToLower().IndexOf("order") > -1))
            {
                qsql += " order by ORDER_NUM";
            }
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleMapModel mim = new CmsArticleMapModel();
                    if (!rdr.IsDBNull(0)) mim.MapId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) mim.ArticleId = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) mim.MapName = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) mim.MapLat = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.MapLog = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.MapContent = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.AddUser = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.AddTime = rdr.GetDateTime(7);
                    if (!rdr.IsDBNull(8)) mim.State = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) mim.Ordernum = rdr.GetInt32(9);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <returns></returns>
        public CmsArticleMapModel GetMap(string mid)
        {
            string qsql = "select ARTICLE_ID,MAP_NAME,MAP_LAT,MAP_LOG,MAP_CONTENT,ADDUSER,ADDTIME,STATE,MAP_ID,ORDER_NUM from COFCO_ARTICLE_MAP where " + mid;
            CmsArticleMapModel mim = null;
       
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, null))
            {
                if (rdr.Read())
                {
                    mim = new CmsArticleMapModel();                    
                    if (!rdr.IsDBNull(0)) mim.ArticleId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) mim.MapName = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) mim.MapLat = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) mim.MapLog = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.MapContent = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.AddUser = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.AddTime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) mim.State = rdr.GetInt32(7);
                    if (!rdr.IsDBNull(8)) mim.MapId = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) mim.Ordernum = rdr.GetInt32(9);
                }
            }
            return mim;
        }        
    }
}
